/*
 * @(#)ExamDAO.java Time: 2013-2-27
 *
 * Copyright 2013 xuedou.com All rights reserved.
 */
package xuedou.skymoni.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sf.json.JSONObject;
import xuedou.skymoni.bean.Exam;
import xuedou.skymoni.bean.Examnum;
import xuedou.skymoni.bean.Examschool;
import xuedou.skymoni.bean.Examtime;
import xuedou.skymoni.util.DBConnection;

/**
 *<pre>类说明</pre>
 *<b>功能描述：</b>
 * 报名考试DAO
 * @author  jinmingming jinmingming@xuedou.com
 * @version 1.0, 2013-2-27
 */
public class ExamDAO{

	private DBConnection db = new DBConnection();
	private Connection conn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	
	/**
	 * 增加
	 */
	
	public boolean addExam(Exam exam) {
		try {
			conn = db.getConnection();
			String sql = "insert into exam(sname,school,tel,issky,examschool,examtime,examnum,ticknum,snumber) values(?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, exam.getSname());
			ps.setString(2, exam.getSchool());
			ps.setString(3, exam.getTel());
			ps.setString(4, exam.getIssky());
			ps.setString(5, exam.getExamschool());
			ps.setString(6, exam.getExamtime());
			ps.setString(7, exam.getExamnum());
			ps.setString(8, exam.getTicknum());
			ps.setString(9, exam.getSnumber());
			ps.execute();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	//增加second
	public boolean addExamSecond(Exam exam) {
		try {
			String sql = "insert into exam(sname,school,classroom,tel,issky,examschool,examtime,examnum,ticknum,snumber) values(?,?,?,?,?,?,?,?,?,?)";
			String sql2="select * from newexamschool where status<>1 and schoolname=? order by id asc limit 1";
			String sql3="update newexamschool set alreadysize=alreadysize+1 where id=?";
			conn = db.getConnection();
			ps=conn.prepareStatement(sql2);
			System.out.print(exam.getExamschool());
			ps.setString(1,exam.getExamschool());
			rs=ps.executeQuery();
			String classroom=null;
			int id=0;
			while(rs.next())
			{
				classroom=rs.getString("classroom");
				id=rs.getInt("id");
				exam.setClassRoom(classroom);
			}
//			db.closeCon(rs, ps, conn);
			if(classroom!=null)
			{
				try
				{
				conn.setAutoCommit(false);
				ps=conn.prepareStatement(sql3);
				ps.setString(1, Integer.toString(id));
				ps.execute();
				ps = conn.prepareStatement(sql);
				ps.setString(1, exam.getSname());
				ps.setString(2, exam.getSchool());
				ps.setString(3, classroom.trim());
				ps.setString(4, exam.getTel());
				ps.setString(5, exam.getIssky());
				ps.setString(6, exam.getExamschool());
				ps.setString(7, "");
				ps.setString(8, exam.getExamnum());
				ps.setString(9, exam.getTicknum());
				ps.setString(10, exam.getSnumber());
				ps.execute();
				conn.commit();
				}
				catch(Exception exception)
				{
					exception.printStackTrace();
					try {
						conn.rollback();
						}
						catch(Exception e){
						e.printStackTrace();
						return false;
						}
						return false;
				}
				return true;
			}
		
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	/**
	 * 根据校区查询每个时间段已报名的人数
	 * @return
	 */
	public List<Object> getStuCountBySchool(String examschool,String examnum){
		List<Object> ar = new ArrayList<Object>();
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("select count(*) as count ,examschool  from exam where examschool = ? and examnum = ? group by examschool");
			ps.setString(1, examschool);
			ps.setString(2, examnum);
			rs = ps.executeQuery();
			while (rs.next())
			{
				JSONObject obj = new JSONObject();
				obj.put("examschool", rs.getString("examschool"));
				obj.put("count", rs.getInt("count"));
				ar.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
	}
	
	/**
	 * 根据校区和时间查询人数
	 */
	public int getStuCountBySchoolTime(String examschool,String examtime,String examnum){
		conn = db.getConnection();
		int count = 0;
		try {
			ps = conn.prepareStatement("select count(*) as count from exam where examschool = ? and examtime = ? and examnum=?");
			ps.setString(1, examschool);
			ps.setString(2, examtime);
			ps.setString(3, examnum);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return count;
	}
	
	
	public static void main(String[] args)
	{
		ExamDAO ex=new ExamDAO();
		System.out.println(ex.getStuCountBySchool_newexamschool("景田", null, null));
	}
	public int getStuCountBySchool_newexamschool(String examschool,String examtime,String examnum)
	{
		
		conn = db.getConnection();
		int count = 0;
		try {
			ps = conn.prepareStatement("select sum(alreadysize) as count from newexamschool where schoolname = ? group by schoolname");
			ps.setString(1, examschool);
//			ps.setString(2, examtime);
//			ps.setString(2, examnum);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return count;
	}
	//根据学校，考试名称查是否已经满了
	public int getStuCountBySchool(String examschool,String examtime,String examnum){
		conn = db.getConnection();
		int count = 0;
		try {
			ps = conn.prepareStatement("select count(*) as count from exam where examschool = ?  and examnum=?");
			ps.setString(1, examschool);
//			ps.setString(2, examtime);
			ps.setString(2, examnum);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return count;
	}
	
	

	/**
	 * 查询总报名人数
	 * @return
	 */
	public int allCount(String examnum,String schoolName){
		conn = db.getConnection();
		int count = 0;
		try {
			ps = conn.prepareStatement("select count(*) as count from exam where examnum = ? and examschool =? ");
			ps.setString(1, examnum);
			ps.setString(2, schoolName);
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt("count");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return count;
	}
	
	/**
	 * 根据姓名,电话，模拟考试，学生编号查询
	 * @param name
	 * @param tel
	 * @return
	 */
	public Exam findByNameTelIsSky(String name,String examnum,String snumber){
		conn = db.getConnection();
		Exam exam = null;
		try {
			ps = conn.prepareStatement("select * from exam where sname=? and examnum = ? and snumber=?");
			ps.setString(1, name);
			ps.setString(2, examnum);
			ps.setString(3, snumber);
			rs = ps.executeQuery();
			if(rs.next()){
				exam = new Exam();
				exam.setId(rs.getInt("id"));
				exam.setSname(rs.getString("sname"));
				exam.setSchool(rs.getString("school"));
				exam.setTel(rs.getString("tel"));
				exam.setIssky(rs.getString("issky"));
				exam.setExamschool(rs.getString("examschool"));
				exam.setExamtime(rs.getString("examtime"));//不用了
				exam.setClassRoom(rs.getString("classroom"));
				exam.setExamnum(rs.getString("examnum"));//考试场次安排
				exam.setTicknum(rs.getString("ticknum"));//准考证号
				exam.setSendcount(rs.getInt("sendcount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return exam;
	}
	
	/**
	 * 根据姓名,电话，模拟考试查询
	 * @param name
	 * @param tel
	 * @return
	 */
	public Exam findByNameTel(String name,String tel,String examnum){
		conn = db.getConnection();
		Exam exam = null;
		try {
			ps = conn.prepareStatement("select * from exam where sname=? and tel=? and examnum = ?");
			ps.setString(1, name);
			ps.setString(2, tel);
			ps.setString(3, examnum);
			rs = ps.executeQuery();
			if(rs.next()){
				exam = new Exam();
				exam.setId(rs.getInt("id"));
				exam.setSname(rs.getString("sname"));
				exam.setSchool(rs.getString("school"));
				exam.setTel(rs.getString("tel"));
				exam.setIssky(rs.getString("issky"));
				exam.setExamschool(rs.getString("examschool"));
				exam.setExamtime(rs.getString("examtime"));
				exam.setClassRoom(rs.getString("classroom"));
				exam.setExamnum(rs.getString("examnum"));
				exam.setTicknum(rs.getString("ticknum"));
				exam.setSendcount(rs.getInt("sendcount"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return exam;
	}
	
	/**
	 * 修改短信次数
	 * @param id
	 * @return
	 */
	public boolean updateSendcount(int id){
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("update exam set sendcount=sendcount+1 where id = ?");
			ps.setInt(1, id);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	
	/**
	 * 查询表
	 */
	public List<Exam> allExam(){
		List<Exam> ar = new ArrayList<Exam>();
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("select * from exam order by examnum,examschool,examtime asc");
			rs = ps.executeQuery();
			while(rs.next()){
				Exam exam = new Exam();
				exam.setId(rs.getInt("id"));
				exam.setSname(rs.getString("sname"));
				exam.setSchool(rs.getString("school"));
				exam.setTel(rs.getString("tel"));
				exam.setIssky(rs.getString("issky"));
				exam.setExamschool(rs.getString("examschool"));
//				exam.setExamtime(rs.getString("examtime"));
				exam.setClassRoom(rs.getString("classroom"));
				exam.setExamnum(rs.getString("examnum"));
				exam.setTicknum(rs.getString("ticknum"));
				exam.setSendcount(rs.getInt("sendcount"));
				//2013/4/5
				exam.setSnumber(rs.getString("snumber"));
				exam.setIspayment(rs.getString("ispayment"));
				ar.add(exam);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
	}
	
	/**
	 * 查询场次
	 */
	public List<Examnum> allExamnum(){
		List<Examnum> ar = new ArrayList<Examnum>();
		conn = db.getConnection();
		Examnum examnum = null;
		try {
			ps = conn.prepareStatement("select * from examnum");
			rs = ps.executeQuery();
			while(rs.next()){
				examnum = new Examnum();
				examnum.setId(rs.getInt("id"));
				examnum.setChangci(rs.getString("changci"));
				examnum.setFlag(rs.getString("flag"));
				ar.add(examnum);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
	}
	
	/**
	 * 修改场次
	 */
	public boolean updateExamnum(int id,String flag){
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("update examnum set flag = ? where id = ?");
			ps.setString(1, flag);
			ps.setInt(2, id);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	
	/**
	 * 查询场次是否开放
	 */
	public String isOpen(String changci){
		conn = db.getConnection();
		if (conn==null)
		{
			return "1";
		}
		String isopen="";
		try {
			ps = conn.prepareStatement("select flag as flag from examnum where changci = ?");
			ps.setString(1, changci);
			rs = ps.executeQuery();
			if(rs.next()){
				isopen = rs.getString("flag");
//				if(isopen.trim().equals("")){
//					return true;
//				}
				return isopen;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return "";
	}
	
	/**
	 * 查询configure
	 */
	public String isSky(String cname){
		conn = db.getConnection();
		String isSky = "";
		try {
			ps = conn.prepareStatement("select cvalue from configure where cname = ?");
			ps.setString(1, cname);
			rs = ps.executeQuery();
			if(rs.next()){
				isSky = rs.getString("cvalue");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return isSky;
	}
	
	/**
	 * 修改configure
	 */
	public boolean updateConfigure(String cname,String cvalue){
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("update configure set cvalue = ? where cname = ?");
			ps.setString(1, cvalue);
			ps.setString(2, cname);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	
	/**
	 * 成绩查询
	 */
	public String[] selectScore(String sname,String ticknum,String examnum){
		String[] result = new String[4];
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("select * from score where sname=? and ticknum=? and examnum=?");
			ps.setString(1, sname);
			ps.setString(2, ticknum);
			ps.setString(3, examnum);
			rs = ps.executeQuery();
			if(rs.next()){
				result[0]=rs.getString("sname");
				result[1]=rs.getString("score");
				result[2]=rs.getString("level");
				return result;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return null;
	}
	
	/**
	 * 准考证号查询是否交费
	 */
	public Exam ispayment(String ticknum,String examnum){
		conn = db.getConnection();
		Exam exam = null;
		try {
			ps = conn.prepareStatement("select * from exam where ticknum=? and examnum=?");
			ps.setString(1, ticknum);
			ps.setString(2, examnum);
			rs = ps.executeQuery();
			if(rs.next()){
				exam = new Exam();
				exam.setId(rs.getInt("id"));
				exam.setSname(rs.getString("sname"));
				exam.setSchool(rs.getString("school"));
				exam.setTel(rs.getString("tel"));
				exam.setIssky(rs.getString("issky"));
				exam.setExamschool(rs.getString("examschool"));
//				exam.setExamtime(rs.getString("examtime"));
				exam.setClassRoom(rs.getString("classroom"));
				exam.setExamnum(rs.getString("examnum"));
				exam.setTicknum(rs.getString("ticknum"));
				exam.setSendcount(rs.getInt("sendcount"));
				exam.setIspayment(rs.getString("ispayment"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return exam;
	}
	
	/**
	 * 修改是否交费
	 */
	public boolean updateIspayment(int id,String ispayment){
		conn = db.getConnection();
		try {
			ps = conn.prepareStatement("update exam set ispayment = ? where id = ?");
			ps.setString(1, ispayment);
			ps.setInt(2, id);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return false;
	}
	
	/**
	 * 查询时间段
	 */
	public List<Examtime> allExamtime(){
		List<Examtime> ar = new ArrayList<Examtime>();
		conn = db.getConnection();
		Examtime examtime = null;
		try {
			ps = conn.prepareStatement("select * from examtime");
			rs = ps.executeQuery();
			while(rs.next()){
				examtime = new Examtime();
				examtime.setId(rs.getInt("id"));
				examtime.setEtime(rs.getString("etime"));
				examtime.setEcode(rs.getString("ecode"));
				ar.add(examtime);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
	}
	
	public List<Examschool> allExamschoolSecond()
	{
		List<Examschool> ar = new ArrayList<Examschool>();
		conn = db.getConnection();
		Examschool examschool = null;
		int i=0;
		try {
			ps = conn.prepareStatement("select DISTINCT schoolname ,schoolcode from newexamschool ");
			rs = ps.executeQuery();
			while(rs.next()){
				examschool = new Examschool();
				examschool.setId(++i);
				examschool.setEschool(rs.getString("schoolname"));
				examschool.setEcode(rs.getString("schoolcode"));
//				examschool.setClasssize(rs.getString("classsize"));
				ar.add(examschool);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
//		return null;
	}
	/**
	 * 查询校区
	 */
	public List<Examschool> allExamschool(){
		List<Examschool> ar = new ArrayList<Examschool>();
		conn = db.getConnection();
		Examschool examschool = null;
		try {
			ps = conn.prepareStatement("select * from examschool");
			rs = ps.executeQuery();
			while(rs.next()){
				examschool = new Examschool();
				examschool.setId(rs.getInt("id"));
				examschool.setEschool(rs.getString("eschool"));
				examschool.setEcode(rs.getString("ecode"));
				examschool.setClasssize(rs.getString("classsize"));
				ar.add(examschool);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return ar;
	}
	
	
	/**
	 * 根据 名称查询学校 人数
	 */
	public int findClasssize(String eschool){
		conn = db.getConnection();
		int count = 0;
		try {
			String sql="select sum(classsize) as scount from newexamschool where schoolname ='"+eschool+"' GROUP BY schoolname";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()){
				count = rs.getInt("scount");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			db.closeCon(rs, ps, conn);
		}
		return count;
	}
	
	
}
